package dao;

import java.sql.*;

public class SC {
	private static Connection connection=DBConnection.getDBCourseConnection();

	public static float getScoreById(String sid,String cid){
	String sql="select score from sc where sid=? and cid=?";
	PreparedStatement preparedStatement= null;
	float score=0;
	try {
		preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1,sid);
		preparedStatement.setString(2,cid);
		ResultSet resultSet=preparedStatement.executeQuery();
		if(resultSet.next()){
			score=resultSet.getFloat("score");
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return score;
	}

	public static boolean hasSid(String sid){
	String sql="select sid from sc where sid=?";
	PreparedStatement preparedStatement= null;
	boolean result=false;
	try {
		preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1,sid);
		ResultSet resultSet=preparedStatement.executeQuery();
		if(resultSet.next()){
			result=true;
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return result;
	}

	public static boolean hasCid(String cid){
	String sql="select cid from sc where cid=?";
	PreparedStatement preparedStatement= null;
	boolean result=false;
	try {
		preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1,cid);
		ResultSet resultSet=preparedStatement.executeQuery();
		if(resultSet.next()){
			result=true;
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return result;
	}

	public static boolean insert(String sid,String cid,float score){
	String sql="insert into sc values(?,?,?)";
	PreparedStatement preparedStatement= null;
	boolean result=false;
	try {
		preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1,sid);
		preparedStatement.setString(2,cid);
		preparedStatement.setFloat(3,score);
		int i=preparedStatement.executeUpdate();
		if(i==1){
			result=true;
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return result;
	}

	public static boolean insert(String sid,int cid){
		String sql="insert into sc values(?,?)";
		PreparedStatement preparedStatement= null;
		boolean result=false;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1,sid);
			preparedStatement.setInt(2,cid);
			int i=preparedStatement.executeUpdate();
			if(i==1){
				result=true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public static boolean dropCourse(String sid, int cid) {
		boolean result=true;
		try {
			CallableStatement addQuestion=connection.prepareCall("{call drop_course(?,?)}");
			addQuestion.setString(1,sid);
			addQuestion.setInt(2,cid);
			addQuestion.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			result=false;
		}
		return result;
	}
}
